/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */


/**
 *
 * @author tboyuktas
 */

    
import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.UUID;

public class MySqlExample {
   
    
  private Connection connect = null;
  private Statement statement = null;
  private PreparedStatement preparedStatement = null;
  private ResultSet resultSet = null;

  public void readDataBase() throws Exception {
    try {
      // this will load the MySQL driver, each DB has its own driver
      Class.forName("com.mysql.jdbc.Driver");
      // setup the connection with the DB.

        connect = DriverManager.getConnection("jdbc:mysql://localhost/test?" + "user=root&password=basak");
// statements allow to issue SQL queries to the database
      statement = connect.createStatement();
      // resultSet gets the result of the SQL query
      resultSet = statement.executeQuery("select * from test.books");
      writeResultSet(resultSet);

      // preparedStatements can use variables and are more efficient
      preparedStatement = connect.prepareStatement("insert into  test.books values (?, ?, ?, ?, ? , ?, ?,?,?)");
    //"myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
      // parameters start with 1
     preparedStatement.setInt(1,1);
      preparedStatement.setString(2, "gg");
      preparedStatement.setDate(3, new java.sql.Date(2009, 12, 11));
     preparedStatement.setInt(4,2);
      preparedStatement.setString(5, "aa");
      preparedStatement.setString(6, "Tisbn");
      preparedStatement.setString(7, "Tisbn");
      preparedStatement.setInt(8, 5);
      preparedStatement.setString(9, "Tisbn");
      preparedStatement.executeUpdate();
      
      preparedStatement = connect.prepareStatement("SELECT * from test.books");
      resultSet = preparedStatement.executeQuery();
      writeResultSet(resultSet);

      // remove again the insert comment
      preparedStatement = connect.prepareStatement("delete from test.books where objid= ? ; ");
      preparedStatement.setInt(1, 1);
      preparedStatement.executeUpdate();
      
      resultSet = statement.executeQuery("select * from test.books");
      writeMetaData(resultSet);
      
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      close();
    }

  }

  private void writeMetaData(ResultSet resultSet) throws SQLException {
    // now get some metadata from the database
    System.out.println("The columns in the table are: ");
    System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
    for  (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
      System.out.println("Column " +i  + " "+ resultSet.getMetaData().getColumnName(i));
    }
  }

  private void writeResultSet(ResultSet resultSet) throws SQLException {
    // resultSet is initialised before the first data set
    while (resultSet.next()) {
      // it is possible to get the columns via name
      // also possible to get the columns via the column number
      // which starts at 1
      // e.g., resultSet.getSTring(2);
      int objId = resultSet.getInt("objid");
      String issueArea = resultSet.getString("issue_area");
     Date date = resultSet.getDate("issue_date");
      int authorCount = resultSet.getInt("author_count");
      String bookName = resultSet.getString("book_name");
      String authorName = resultSet.getString("author_name");
      String ISBN = resultSet.getString("isbn");
      String publisherName = resultSet.getString("articlesarticlesarticlespublisherName");
      
      System.out.println("ObjectID: " + objId);
      System.out.println("Issue Area: " + issueArea);
      System.out.println("Author Count: " + authorCount);
      System.out.println("Date: " + date);
      System.out.println("Author NAme: " + authorName);
      System.out.println("Book NAme: " + bookName);
      System.out.println("ISBN: " + ISBN);
      System.out.println("Publisher NAme: " + publisherName);
    }
  }

  // you need to close all three to make sure
  private void close() {
    close(resultSet);
    close((ResultSet) statement);
    close((ResultSet) connect);
  }
  private void close(Closeable c) {
    try {
      if (c != null) {
        c.close();
      }
    } catch (IOException e) {
    // don't throw now as it might leave following closables in undefined state
    }
  }

    private void close(ResultSet resultSet) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
    public static void main(String[] args){
        try{
    new MySqlExample().readDataBase();}
        catch(Exception e){System.out.print(e.getMessage());}
    }

}

    
    
 
    /**
     * @param args the command line arguments
     */
   
